import pymysql
import sqlparse
from dotenv import dotenv_values

config = dotenv_values('.flaskenv')  # 加载 .flaskenv 文件中定义的环境变量
# MySql配置信息
HOST = config.get('MYSQL_HOST') or '127.0.0.1'
PORT = config.get('MYSQL_PORT') or 3306
DATABASE = config.get('MYSQL_DATABASE') or 'AdminFlask'
USERNAME = config.get('MYSQL_USERNAME') or 'root'
PASSWORD = config.get('MYSQL_PASSWORD')


def is_exist_database():
    '''

    Returns: 通过统计数据库中表的数量判断数据库是否为空

    '''
    db = pymysql.connect(
        host=HOST,
        port=int(PORT),
        user=USERNAME,
        password=PASSWORD,
        charset='utf8mb4')
    cursor = db.cursor()
    sql = "SELECT COUNT(DISTINCT `TABLE_NAME`) AS anyAliasName FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `table_schema` = '%s';" % DATABASE
    # 获取数据库中不同表的数量
    res = cursor.execute(sql)
    results = cursor.fetchall()  # 获取执行查询后返回的所有结果集

    db.close()
    return results


def init_database():
    '''

    Returns: 当数据库不存在时创建

    '''
    db = pymysql.connect(
        host=HOST,
        port=int(PORT),
        user=USERNAME,
        password=PASSWORD,
        charset='utf8mb4')
    cursor = db.cursor()
    # 当数据库不存在时创建数据库
    sql = "CREATE DATABASE IF NOT EXISTS %s CHARSET=utf8 COLLATE=utf8_general_ci;" % DATABASE
    res = cursor.execute(sql)
    db.close()
    return res


def table_exists(cursor, table_name):
    cursor.execute(f"SHOW TABLES LIKE '{table_name}'")
    result = cursor.fetchone()
    return result is not None


def drop_table(cursor, table_name):
    try:
        for table in table_name:
            cursor.execute(f"DROP TABLE IF EXISTS {table}")
            print(f"Table {table} dropped successfully.")
    except Exception as e:
        print(f"Failed to drop table {table}: {e}")


def execute_fromfile(filename):
    """

    Args:
        filename: sql文件名

    Returns: 创建表

    """
    db = pymysql.connect(
        host=HOST,
        port=int(PORT),
        user=USERNAME,
        password=PASSWORD,
        database=DATABASE,
        charset='utf8')
    fd = open(filename, 'r', encoding='utf-8')
    cursor = db.cursor()
    sqlfile = fd.read()
    sqlfile = sqlparse.format(sqlfile, strip_comments=True).strip()

    tables = ['alike', 'clike', 'picture', 'comment', 'afavor', 'article', 'user', 'type', "aview"]
    for table in tables:
        if not table_exists(cursor, table):
            tables.remove(table)
            drop_table(cursor, tables)
            break

    sqlcommands = sqlfile.split(';')

    for command in sqlcommands:
        try:
            if 'CREATE TABLE' in command:
                table_name = command.split('CREATE TABLE')[1].split('(')[0].strip()
                if not table_exists(cursor, table_name):
                    # print(f'command = {command}')
                    cursor.execute(command)
                    db.commit()
                    print(f"{table_name}表已经创建")
                else:
                    print(f"{table_name}表已经存在")
            else:
                cursor.execute(command)
                db.commit()

        except Exception as msg:
            print(f"msg = {msg}")
            db.rollback()
    db.close()


def init_type():
    """
    初始化type表
    """
    db = pymysql.connect(
        host=HOST,
        port=int(PORT),
        user=USERNAME,
        password=PASSWORD,
        database=DATABASE,
        charset='utf8')

    cursor = db.cursor()
    sql = "INSERT INTO type (name, fid) VALUES (%s, %s)"
    datas = [("文章", None), ("公告", None), ("任务", None), ("已经被领取的任务", 3)]

    try:
        cursor = db.cursor()
        # 检查表是否为空
        cursor.execute("SELECT COUNT(*) FROM type")
        count = cursor.fetchone()[0]

        if count == 0:
            # 表为空时插入数据
            for data in datas:
                cursor.execute(sql, data)
            db.commit()
            print("type表为空，已初始化数据：")

    except Exception as e:
        print("发生错误：", e)
        db.rollback()
    finally:
        # 关闭游标和连接
        cursor.close()
        db.close()


def init_sql():
    '''

    初始化数据库

    '''
    if is_exist_database()[0][0] > 0:
        print('数据库%s不为空，不创建数据库' % str(DATABASE))
    else:
        init_database()
        print('数据库%s创建成功' % str(DATABASE))
    execute_fromfile('init_db.sql')
    init_type()
    print('初始化数据库成功')
